package drugdealer.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import drugdealer.exception.UserTypeException;
import drugdealer.model.Receita;

/**
 * Class to manage database table PacienteReceitas and
 * Receita object related database management.
 * 
 * @author Dave
 *
 */
public class ReceitaDAO {
	/**
	 * Static method 
	 * 
	 * @param receita
	 * @return
	 */
	public static int insert(Receita receita) {
		// Get ConnectionPool
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();

		PreparedStatement ps = null;

		// Query templates
		String queryUsuarioDados = "INSERT INTO PacienteReceita (Nome, Receita, Email)"
				+ "VALUES (?, ?, ?)";

		try {
			// Populate the query template for ReceitaPaciente table
			ps = connection.prepareStatement(queryUsuarioDados);
			ps.setString(1, receita.getNome());
			ps.setString(2, receita.getReceita());
			ps.setString(3, receita.getEmailPaciente());

			// Insert data into UsuarioDados table
			return ps.executeUpdate();
		} catch (SQLException sqle) {
			// todo exceptions throwing
			sqle.printStackTrace();
		} finally {
			pool.freeConnection(connection);
		}
		return 0;
	}

	public static ArrayList<Receita> consultarReceitas(String email)
			throws UserTypeException {
		// DB connection set up
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ArrayList<Receita> receitas = new ArrayList<Receita>();

			// Query templates
			String query = "SELECT * FROM " + "PacienteReceita "
					+ "WHERE Email=?";

			// Set query string with receita name
			ps = connection.prepareStatement(query);
			ps.setString(1, email);

			// Execute query to retrieve receita list from DB
			rs = ps.executeQuery();

			// Create Receita object for each receita in the list
			// then add the object into ArrayList
			while (rs.next()) {
				Receita r = new Receita();
				r.setNome(rs.getString("nome"));
				r.setReceita(rs.getString("receita"));
				r.setEmailPaciente(rs.getString("email"));

				receitas.add(r);
			}

			return receitas;
		} catch (SQLException sqle) {
			// todo exceptions throwing
			sqle.printStackTrace();
		} finally {
			pool.freeConnection(connection);
		}
		return null;
	}
}
